/*
Thong tin nhom
Ma nhom: 20
1241336 - Nguyen Trung Hau
1241393 - Dam Thieu Quang
1241444 - Nguyen Thi Yen
*/

--4.1
CREATE PROCEDURE sp_ThongtinDocGia @MA smallint
AS
BEGIN
	IF EXISTS (SELECT
			ma_docgia
		FROM NguoiLon NL
		WHERE ma_docgia = @MA)
	BEGIN
		SELECT DISTINCT
			DG.ma_docgia AS Mã,
			DG.ho AS Họ,
			DG.tenlot AS "Tên lót",
			DG.ten AS Tên,
			DG.NgaySinh AS "Ngày Sinh",
			NL.sonha AS "Số nhà",
			NL.duong AS "Đường",
			NL.quan AS "Quận",
			NL.dienthoai AS "Điện Thoại",
			NL.han_sd AS "Hạn Dùng"
		FROM	DocGia DG,
				NguoiLon NL
		WHERE DG.ma_docgia = @MA
	END
	ELSE
	BEGIN
		SELECT
			*
		FROM	DocGia DG,
				TreEm TE
		WHERE DG.ma_docgia = TE.ma_docgia
	END
END
GO

	EXEC sp_ThongtinDocGia 1

	--4.2 --Da check

CREATE PROCEDURE sp_ThongtinDausach @isbn int
AS
BEGIN
	SELECT
		ts.TuaSach,
		ts.tacgia,
		ds.ngonngu,
		ds.bia,
		ds.trangthai,
		ts.tomtat,
		COUNT(*) AS SachChuaMuon
	FROM	DauSach ds,
			TuaSach ts,
			CuonSach cs
	WHERE ds.ma_tuasach = ts.ma_tuasach AND ds.isbn = cs.isbn AND ds.isbn = @isbn
	GROUP BY	ds.isbn,
				ts.TuaSach,
				ts.tacgia,
				ds.ngonngu,
				ds.bia,
				ds.trangthai,
				ts.tomtat
END
GO
	EXEC sp_ThongtinDausach '3'
SELECT * FROM DocGia dg

--4.3 đã check
ALTER PROCEDURE sp_ThongtinNguoilonDangmuon
AS
BEGIN
	SELECT DISTINCT
		dg.*
	FROM DocGia dg
	JOIN Muon m
		ON dg.ma_docgia = m.ma_docgia
END
	EXEC sp_ThongtinNguoilonDangmuon

	--4.4 đã check
	ALTER PROC sp_ThongtinNguoilonQuahan
	AS
	BEGIN
		SELECT
			dg.ma_docgia,
			dg.ho,
			dg.tenlot,
			dg.ten,
			dg.NgaySinh
		FROM	DocGia dg,
				Muon m
		WHERE m.ma_docgia = dg.ma_docgia AND m.ngay_hethan < GETDATE()
	END
GO
	--EXEC sp_ThongtinNguoilonQuahan

--4.5

CREATE PROCEDURE sp_DocGiaCoTreEmMuon
AS
BEGIN
	SELECT DISTINCT
		dg.ma_docgia,
		dg.ho,
		dg.tenlot,
		dg.ten,
		nl.duong,
		nl.quan,
		nl.dienthoai,
		nl.han_sd
	FROM treem te
	JOIN muon m
		ON te.ma_docgia = m.ma_docgia
	JOIN nguoilon nl
		ON nl.ma_docgia = te.ma_docgia_nguoilon
	JOIN muon m1
		ON nl.ma_docgia = m1.ma_docgia
	JOIN docgia dg
		ON dg.ma_docgia = nl.ma_docgia
END
GO
--			EXEC sp_DocGiaCoTreEmMuon

--4.6 
ALTER PROCEDURE sp_CapnhatTrangthaiDausach @isbn int
AS
BEGIN TRAN
	IF ((SELECT
			COUNT(ma_cuonsach)
		FROM cuonsach
		WHERE isbn = @isbn)
		- (SELECT
			COUNT(ma_cuonsach)
		FROM cuonsach
		WHERE tinhtrang = 'N' AND isbn=@isbn)
		= 0)
	BEGIN
		UPDATE dausach
		SET trangthai = 'N'
		WHERE isbn = @isbn
		WAITFOR DELAY '00:00:10'
	END
	ELSE
	BEGIN
		UPDATE dausach
		SET trangthai = 'Y'
		WHERE isbn = @isbn
		WAITFOR DELAY '00:00:10'
	END
	
ROLLBACK TRAN
GO

SELECT * FROM CuonSach cs
EXEC sp_CapnhatTrangthaiDausach 1
SELECT * FROM DauSach ds

--4.7
ALTER PROCEDURE sp_ThemTuaSach @TuaSach nvarchar(63), @TacGia nvarchar(31), @TomTat nvarchar(222)
AS
BEGIN
	DECLARE @MA int
	SET @MA = 1
	WHILE @MA IN (SELECT
			ma_tuasach
		FROM tuasach)
	BEGIN
		SET @MA = @MA + 1
	END
	IF (@TuaSach IS NULL OR @TacGia IS NULL OR @TomTat IS NULL)
	BEGIN
		PRINT N'Tựa sách nhập không đúng quy định'
	END
	ELSE
	BEGIN
		
		INSERT INTO tuasach
			VALUES (@MA, @TuaSach, @TacGia, @TomTat)
	END
	WAITFOR DELAY '00:00:01'
END
GO

DELETE FROM TuaSach WHERE ma_tuasach = 51

SELECT * FROM TuaSach ts WHERE ts.tacgia='Pearl S. Buck'
EXEC sp_ThemTuaSach	N'Anh văn 3','Pearl S. Buck','b'
SELECT * FROM TuaSach ts WHERE ts.tacgia='Pearl S. Buck'

--4.8 Da check
CREATE PROC sp_ThemCuonSach @isbn int
AS
BEGIN
	DECLARE	@flag int,
			@MA smallint
	SET @flag = 1
	IF NOT EXISTS (SELECT
			*
		FROM DauSach ds
		WHERE ds.isbn = @isbn)
	BEGIN
		PRINT N'Đầu sách không tồn tại'
		RETURN
	END
	ELSE
	BEGIN
		DECLARE @cur CURSOR
		SET @cur = CURSOR FOR (SELECT
			cs.Ma_CuonSach
		FROM CuonSach cs
		WHERE cs.isbn = @isbn)
		OPEN @cur
		FETCH NEXT FROM @cur INTO @MA
		WHILE @@fetch_status = 0
		BEGIN
			IF (@MA - @flag > 0)
			BEGIN
				BREAK
			END
			SET @flag = @flag + 1;
			FETCH NEXT FROM @cur INTO @MA
		END
		INSERT INTO CuonSach (isbn, Ma_CuonSach, TinhTrang)
			VALUES (@isbn, @flag, 'Y')
		UPDATE DauSach
		SET trangthai = 'Y'
		WHERE isbn = @isbn
		PRINT N'Thêm thành công!!!'
		CLOSE @cur
		DEALLOCATE @cur
	END
	
END
GO
--EXEC sp_ThemCuonSach 3


--4.9 Đã check
ALTER PROCEDURE sp_ThemNguoilon @ho nvarchar(15), @tenlot nvarchar(1), @ten nvarchar(15), @ngaysinh smalldatetime,
@sonha nvarchar(15), @duong nvarchar(63), @quan nvarchar(2), @dienthoai nvarchar(13), @hansd smalldatetime, @info nvarchar(30) OUT
AS
BEGIN

	DECLARE @I integer
	SET @I = 1;
	WHILE EXISTS (SELECT
			DG.ma_docgia
		FROM DocGia DG
		WHERE DG.ma_docgia = @I)
	BEGIN
		SET @I = @I + 1;
	END
	INSERT INTO DocGia
		VALUES (@I, @ho, @tenlot, @ten, @ngaysinh)
	IF ((YEAR(GETDATE()) - YEAR(@ngaysinh)) < 18)
	BEGIN
		SET @info = N'Không đủ tuổi';
		RETURN
	END
	ELSE
	BEGIN
		INSERT INTO NguoiLon
			VALUES (@I, @sonha, @duong, @quan, @dienthoai, @hansd)
		SET @info = N'Thêm thành công';
	END
END
GO

SELECT * FROM DocGia dg WHERE dg.ten LIKE 'Thanh'

SET DATEFORMAT dmy
DECLARE @INFO2 nvarchar(30)
EXEC sp_ThemNguoilon	"La","Van","Cau",'5/8/1989 11:56:39',"17","Lê Đại Hành","5","0154231478","22/12/2014",@INFO2

--4.10 Đã check
CREATE PROCEDURE sp_ThemTreEm @MaNL smallint, @ho nvarchar(15), @tenlot nvarchar(1), @ten nvarchar(15), @ngaysinh smalldatetime,
@sonha nvarchar(15), @duong nvarchar(63), @quan nvarchar(2), @dienthoai nvarchar(13), @hansd smalldatetime, @info nvarchar(30) OUT
AS
BEGIN

	DECLARE @I integer
	SET @I = 1;
	WHILE EXISTS (SELECT
			DG.ma_docgia
		FROM DocGia DG
		WHERE DG.ma_docgia = @I)
	BEGIN
		SET @I = @I + 1;
	END
	INSERT INTO DocGia
		VALUES (@I, @ho, @tenlot, @ten, @ngaysinh)
	DECLARE @S smallint
	SET @S = (SELECT
		COUNT(*)
	FROM TreEm TE
	WHERE TE.ma_docgia_nguoilon = @MaNL)
	SELECT
		@S
	IF NOT EXISTS (SELECT
			ma_docgia
		FROM NguoiLon
		WHERE ma_docgia = @MaNL)
		OR @S >= 2
	BEGIN

		SET @info = N'Ngườilon da co 2 tre em hoac Khong phai nguoi lon';

	END
	IF @S IS NOT NULL AND @S < 2 AND EXISTS (SELECT
			ma_docgia
		FROM NguoiLon
		WHERE ma_docgia = @MaNL)
	BEGIN
		INSERT INTO TreEm
			VALUES (@I, @MaNL)

	END
END
GO

--							SET DATEFORMAT dmy
--							DECLARE @KQ nvarchar(30)
--							EXEC sp_ThemTreEm	"5",
--												"Nguyen",
--												"Van",
--												"Hai",
--												'5/8/2012 11:56:39',
--												"17",
--												"Lê Đại Hành",
--												"5",
--												"0154231478",
--												"22/12/2014",
--												@KQ OUT
--							SELECT
--								@KQ

--4.11

CREATE PROCEDURE sp_XoaDocGia @MA smallint, @KQ nvarchar(30) OUT
AS
BEGIN
	IF EXISTS (SELECT
			ma_docgia
		FROM DocGia DG
		WHERE ma_docgia = @MA)
	BEGIN
		IF NOT EXISTS (SELECT
				DG.ma_docgia
			FROM	DocGia DG,
					Muon m
			WHERE DG.ma_docgia = @MA AND DG.ma_docgia = m.ma_docgia AND QTM.ngayGio_tra IS NULL)
		BEGIN
			IF EXISTS (SELECT
					ma_docgia
				FROM NguoiLon NL
				WHERE ma_docgia = @MA)
			BEGIN
				IF NOT EXISTS (SELECT
						ma_docgia
					FROM TreEM EM
					WHERE ma_docgia_nguoilon = @MA)
				BEGIN
					DELETE FROM NguoiLon
					WHERE ma_docgia = @MA
					DELETE FROM muon
					WHERE ma_docgia = @MA
					DELETE FROM DangKy
					WHERE ma_docgia = @MA
					DELETE FROM DocGia
					WHERE ma_docgia = @MA
				END


				ELSE

				BEGIN
					DECLARE @MA1 smallint
					DECLARE @c CURSOR
					SET @C = CURSOR FOR
					SELECT
						TE.ma_docgia
					FROM TreEm TE
					WHERE TE.ma_docgia_nguoilon = @MA
					OPEN @C
					FETCH NEXT FROM @C INTO @MA1
					WHILE @@fetch_status = 0
					BEGIN
						DELETE FROM TreEm
						WHERE ma_docgia = @MA1
						DELETE FROM muon
						WHERE ma_docgia = @MA1
						DELETE FROM DangKy
						WHERE ma_docgia = @MA1
						DELETE FROM DocGia
						WHERE ma_docgia = @MA1
						FETCH NEXT FROM @C INTO @MA1
					END
					CLOSE @C
					DEALLOCATE @C

					DELETE FROM NguoiLon
					WHERE ma_docgia = @MA
					DELETE FROM muon
					WHERE ma_docgia = @MA
					DELETE FROM DangKy
					WHERE ma_docgia = @MA
					DELETE FROM DocGia
					WHERE ma_docgia = @MA

				END


			END
			ELSE
			BEGIN
				DELETE FROM TreEm
				WHERE ma_docgia = @MA
				DELETE FROM muon
				WHERE ma_docgia = @MA
				DELETE FROM DangKy
				WHERE ma_docgia = @MA
				DELETE FROM DocGia
				WHERE ma_docgia = @MA
			END
		END
		ELSE
			SET @KQ = N'Độc giả vẫn đang mượn sách'

	END
	ELSE
		SET @KQ = N'Không tồn tại đọc giả'
END
GO
--	DECLARE @out nvarchar(30)
--								EXEC sp_XoaDocGia	19,
--													@out OUT
--								SELECT
--									@out

--4.12 --Da check nhung chua chay thu

CREATE PROC sp_MuonSach @madocgia smallint, @isbn int
AS
BEGIN
	IF EXISTS (SELECT
			*
		FROM Muon
		WHERE isbn = @isbn AND ma_docgia = @madocgia)
	BEGIN
		PRINT 'Doc gia dang muon sach cung loai'
		RETURN
	END
	ELSE
	BEGIN
		--Kiem tra so sach doc gia dang muon:

		DECLARE	@sachdangmuon int,
				@docgiamuon int,
				@flag int
		SET @sachdangmuon = 0
		SET @flag = 0
		SET @docgiamuon = 0

		SELECT
			@docgiamuon = COUNT(*)
		FROM Muon m
		WHERE m.ma_docgia = @madocgia AND m.ngay_hethan > GETDATE()
		PRINT @docgiamuon

		--Kiem tra docgia la nguoilon
		IF @madocgia IN (SELECT
				ma_docgia
			FROM NguoiLon)
		BEGIN
			SELECT
				@flag = COUNT(*)
			FROM	Muon m,
					TreEm te
			WHERE m.ma_docgia = te.ma_docgia AND m.ngay_hethan > GETDATE()
			AND te.ma_docgia_nguoilon = @madocgia

			SET @sachdangmuon = @docgiamuon + @flag
			PRINT @sachdangmuon

			IF (@sachdangmuon > 5)
			BEGIN
				PRINT N'Độc giả đã mượn tối đa 5 quyển.'
				RETURN
			END
		END

		--Kiem tra docgia la treem
		ELSE
		IF @madocgia IN (SELECT
				ma_docgia
			FROM TreEm)
		BEGIN
			SET @sachdangmuon = 0
			SET @flag = 0
			PRINT @docgiamuon
			IF (@docgiamuon = 1)
			BEGIN
				PRINT N'Độc giả đã mượn tối đa 1 quyển.'
				RETURN
			END
			ELSE
			IF (@docgiamuon >= 0 AND @docgiamuon < 1)
			BEGIN
				SELECT
					@flag = COUNT(*)
				FROM	Muon m,
						TreEm te
				WHERE m.ma_docgia = te.ma_docgia_nguoilon AND m.ngay_hethan > GETDATE()
				AND te.ma_docgia = @madocgia

				SET @sachdangmuon = @docgiamuon + @flag
				PRINT @sachdangmuon
				IF (@sachdangmuon = 5)
				BEGIN
					PRINT N'Người bảo lãnh độc giả đã mượn tối đa 5 quyển.'
					RETURN
				END
			END
		END
		--Kiem tra con sach trong thu vien khong?
		DECLARE @cur CURSOR
		SET @cur = CURSOR FOR (SELECT
			Ma_CuonSach
		FROM CuonSach cs
		WHERE cs.isbn = @isbn AND cs.TinhTrang = 'Y')

		OPEN @cur
		FETCH NEXT FROM @cur INTO @flag
		IF @@fetch_status = 0
		BEGIN
			--insert bang Muon
			INSERT INTO Muon (isbn, ma_cuonsach, ma_docgia, ngayGio_muon, ngay_hethan)
				VALUES (@isbn, @flag, @madocgia, GETDATE(), DATEADD(dd, 14, GETDATE()))

			--Cap nhat tinh trang Cuonsach
			UPDATE CuonSach
			SET TinhTrang = 'N'
			WHERE Ma_CuonSach = @flag

			--Cap nhat trang thai Dausach
			EXEC sp_CapNhatTrangThaiDauSach @isbn
			PRINT N'Mượn sách thành công.'
		END
		ELSE
		BEGIN
			PRINT N'Khong còn cuốn sách độc giả cần mượn.Độc giả sẽ được đăng kí mượn.'
			INSERT INTO DangKy (isbn, ma_docgia, ngaygio_dk, ghichu)
				VALUES (@isbn, @madocgia, GETDATE(), NULL)
		END
		CLOSE @cur
		DEALLOCATE @cur
	END
END
GO
--								EXEC sp_MuonSach	3,
--													3

--4.13
SELECT
	*
FROM quatrinhmuon
SELECT
	*
FROM muon

ALTER PROCEDURE sp_TraSach @isbn int, @ma_cuonsach int
AS
BEGIN
	DECLARE @ngay_hethang datetime
	DECLARE @tienPhat int

	SELECT
		@ngay_hethang = ngay_hethan
	FROM muon
	WHERE isbn = @isbn AND ma_cuonsach = @ma_cuonsach
	IF (DATEDIFF(DAY, @ngay_hethang, GETDATE()) > 0)
	BEGIN
		SET @tienPhat = DATEDIFF(DAY, @ngay_hethang, GETDATE()) * 1000
		PRINT N'Thông báo: Số tiền phạt trễ hạn của bạn: ' + CAST(@tienPhat AS nchar(10))
	END

	INSERT INTO quatrinhmuon (isbn, ma_cuonsach, ngayGio_muon, ma_docgia, ngay_hethan, ngayGio_tra)
		SELECT
			isbn,
			ma_cuonsach,
			ngayGio_muon,
			ma_docgia,
			ngay_hethan,
			GETDATE()
		FROM muon
		WHERE isbn = @isbn AND ma_cuonsach = @ma_cuonsach

	DELETE FROM muon
	WHERE isbn = @isbn AND ma_cuonsach = @ma_cuonsach
END

--									EXEC sp_TraSach	1,
--													3
--
--									SELECT
--										*
--									FROM quatrinhmuon